package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.SchoolDAO;
import matlap.shared.School;

public class SchoolJdbcDAO implements SchoolDAO {

	Connection con = null;

	public int getSchoolsNumber() {
		int nr = 0;
		String query = "SELECT COUNT(school_name) AS darab FROM school";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

	public List<School> getSchools() {
		List<School> list = new ArrayList<School>();
		String schoolName;
		String localityName;
		String query = "SELECT school_name AS school, locality_name AS locality FROM school,locality WHERE locality.localityID=school.localityID ORDER BY school ";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				schoolName = rs.getString("school");
				localityName = rs.getString("locality");
				School teach = new School(schoolName, localityName);

				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<School> selectSchoolsName() {
		List<School> list = new ArrayList<School>();
		String schoolName;
		String query = "SELECT school_name FROM school ORDER BY school_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				schoolName = rs.getString("school_name");
				School teach = new School(schoolName, null);

				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<String> selectSchoolsNameByLocality(String localityName) {
		List<String> list = new ArrayList<String>();
		String schoolName;
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			int localityID = this.getLocalityId(localityName);
			String query = "SELECT school_name FROM school WHERE localityID = "
					+ localityID +" ORDER BY school_name";
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				schoolName = rs.getString("school_name");

				list.add(i, schoolName);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<School> selectSchoolsLocality() {
		List<School> list = new ArrayList<School>();
		String locality;
		String query = "SELECT DISTINCT locality_name FROM school,locality WHERE school.localityID=locality.localityID ORDER BY locality_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				locality = rs.getString("locality_name");
				School sch = new School(null, locality);

				list.add(i, sch);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<String> selectLocality() {
		List<String> list = new ArrayList<String>();
		String locality;
		String query = "SELECT locality_name FROM locality ORDER BY locality_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				locality = rs.getString("locality_name");

				list.add(i, locality);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public List<School> getSchoolsBy(String schoolName, String localityName) {
		String school = "";
		String loc = "";
		List<School> list = new ArrayList<School>();
		String query = "";
		if (schoolName.length() == 0)
			if (localityName.length() == 0) {
				query = "SELECT school_name,locality_name FROM school,locality WHERE school.localityID=locality.localityID ORDER BY school_name, locality_name";
			} else {
				query = "SELECT school_name,locality_name FROM school,locality WHERE school.localityID=locality.localityID"
						+ " and locality_name='" + localityName + "' ORDER BY school_name, locality_name";
			}
		else if (localityName.length() == 0) {
			query = "SELECT school_name,locality_name FROM school,locality WHERE school.localityID=locality.localityID "
					+ " and school_name like '%" + schoolName + "%' ORDER BY school_name, locality_name";
		} else {
			query = "SELECT school_name,locality_name FROM school,locality WHERE school.localityID=locality.localityID"
					+ " and locality_name='" + localityName
					+ "' and school_name like '%" + schoolName + "%' ORDER BY school_name, locality_name";
		}

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				school = rs.getString("school_name");
				loc = rs.getString("locality_name");
				School teach = new School(school, loc);

				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public int getSchoolId(String schoolName) {

		int schoolID = -1;
		String query = "SELECT schoolID FROM school WHERE school_name='"
				+ schoolName + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				schoolID = rs.getInt("id");

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return schoolID;

	}

	public int getLocalityId(String localityName) {
		int id = -1;
		String query = "SELECT localityID AS id FROM locality WHERE locality_name='"
				+ localityName + "'";
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				id = rs.getInt("id");

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return id;
	}

	public void insertSchool(String schoolName, String localityName) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			PreparedStatement ps = null;
			String s = new String("INSERT INTO school VALUES (?,?,?)");
			ps = con.prepareStatement(s);
			int id = getLocalityId(localityName);

			ps.setString(1, null);
			ps.setString(2, schoolName);
			ps.setInt(3, id);

			ps.executeUpdate();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}
